Re: [SQL] Trouble with massive select statement.

At 15:08 +0300 on 22/06/1999, Darren Greer wrote:


> See below
>
> > > select distinct username, station
> > >     from testmaster t1
> > >     where 1 < (
> > >         select count(t2.username)
> > >             from testmaster t2
> > >             where t2.test_date > '05-14-1999'
> > >             and t2.station = 'WZZZ'
> > >             and t1.username = t2.username
> > >         )
> > > ;
> >
> > The above doesn't seem to me to do what you claim you want to do ---
> > it seems to be looking for users who have taken the test *more than
> > once* in the given interval.
> That is correct......my mistake :)
>
> > Assuming that the code is right and the comment wrong ;-), I agree
> Good assumption :)
>
> > SELECT username, station FROM testmaster
> >   WHERE test_date > '05-14-1999' and station = 'WZZZ'
> >   GROUP BY username, station
> >   HAVING count(*) > 1;
> This works wonderfully, thanks.  Now comes the other twist.  I already have a
> select statement (select first_name, email from listeners l,
>listeners_data ld
> where l.username = ld.username a nd $filter;).  The $filter is a bunch of and
> statements that limit the data coming from the table listeners.  I need
>to get
> the select statement you told me to work as a filter on the data that this
> select statement grabs.  I cant see an easy way to do this without making
>this
> thing incredibly slow having to chech every user.  Any thoughts?

Perhaps you should go with your original scheme. Something like this:

SELECT first_name, email
FROM listeners l, listeners_data ld
WHERE l.username = ld.username AND 1 < (     SELECT count (*)     FROM testmaster t     WHERE t.test_date >
'05-14-1999'      AND t.station = 'WZZZ'       AND t.username = l.username );
 

Now, assuming there is an index on the username field in testmaster, the
internal select will only look for the proper username in testmaster. I
wouldn't say that this is very heavy for a subquery.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma